<cfcomponent>

<cffunction access="public" name="chart_states_1">
<cfquery datasource="#session.dsn#" name="query_results" maxrows="26">
select count(poc_id) as total_state, state 
from poc_detail 
group by state 
order by state asc
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction access="public" name="chart_states_2">
<cfquery datasource="#session.dsn#" name="query_results" maxrows="26">
select count(poc_id) as total_state, state 
from poc_detail
group by state 
order by state desc
</cfquery>
<cfreturn query_results>
</cffunction>


<cffunction access="public" name="chart_discipline">
<cfquery datasource="#session.dsn#" name="query_results">
select count(poc_id) as total_discipline, discipline 
from poc_detail  
group by discipline 
order by discipline desc
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction access="public" name="category">
<cfquery datasource="#session.dsn#" name="query_results">
select five_star_area_id as category_id, area as category
from five_star_area_category  
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction access="public" name="chart_expertise">
<cfquery datasource="#session.dsn#" name="query_results">
select count(poc_id) as total_expertise, overall_level_of_expertise as expertise 
from poc_detail  
group by overall_level_of_expertise 
order by overall_level_of_expertise desc
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction access="public" name="chart_gov_type">
<cfquery datasource="#session.dsn#" name="query_results">
select count(poc_id) as total_gov_type, gov_type 
from poc_detail  
group by gov_type 
order by gov_type desc
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction access="public" name="chart_poc_type_years">
<cfquery datasource="#session.dsn#" name="query_results">
select avg(yrs_five_star_area_experience) as avg_years, poc_type
from poc_detail  
GROUP by poc_type 
order by poc_type desc
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction access="public" name="chart_poc_type">
<cfquery datasource="#session.dsn#" name="query_results">
select count(poc_id) as total_poc, poc_type 
from poc_detail  
group by poc_type 
order by poc_type desc
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction access="public" name="chart_company">
<cfquery datasource="#session.dsn#" name="query_results">
select count(poc_id) as total_company, company 
from poc_detail  
group by company 
order by company
</cfquery>
<cfreturn query_results>
</cffunction>

<cffunction name="colorList" returntype="any">
<cfset columnSort = ''>
<cfset direction = ''>
<cfset rnd = RandRange(1,6)>
<cfif rnd eq 1>
<cfset columnSort = 'color_name'>
<cfset direction = 'asc'>
<cfelseif  rnd eq 2>
<cfset columnSort = 'color_value'>
<cfset direction = 'asc'>
<cfelseif  rnd eq 3>
<cfset columnSort = 'id'>
<cfset direction = 'asc'>
<cfelseif rnd eq 4>
<cfset columnSort = 'color_name'>
<cfset direction = 'desc'>
<cfelseif  rnd eq 5>
<cfset columnSort = 'color_value'>
<cfset direction = 'desc'>
<cfelseif  rnd eq 6>
<cfset columnSort = 'id'>
<cfset direction = 'desc'>
<cfelse>
<cfset columnSort = 'color_name'>
<cfset direction = 'desc'>
</cfif>

<cfquery datasource="#session.dsn#" name="get">
select * from colors order by #columnSort# #direction#
</cfquery>
<cfset newList = ''>
<cfloop query="get" >
<cfset newList = newList & "##" & get.color_value & ",">
</cfloop>
<cfset newList = session.helpers.remove_comma_from_end_of_string(newList)>
<cfreturn newList>
</cffunction>

<cffunction name="colorList2" returntype="any">

<cfquery datasource="#session.dsn#" name="get">
select * from colors
</cfquery>
<cfset newList = ''>
<cfloop query="get" >
<cfset newList = newList & "##" & get.color_value & ",">
</cfloop>
<cfset newList = session.helpers.remove_comma_from_end_of_string(newList)>
<cfreturn newList>
</cffunction>

<cffunction name="make_IDList" returntype="any">
<cfargument required="no" name="list" default="">
<cfset newList = ''>
<cfloop list="#list#" index="i">
<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from five_star_area_category
where area = '#i#'
</cfquery>
<cfset newList = newList & search_results.five_star_area_id & ",">
</cfloop>
<cfreturn newList>
</cffunction>

<cffunction name="make_pList" returntype="any">
<cfargument required="no" name="list" default="">
<cfset newList = ''>
<cfloop list="#list#" index="i">

<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from five_star_area_category
where area = '#i#'
</cfquery>
<cfset newList = newList & session.queries.fiveStarArea(search_results.five_star_area_category_id) & ",">
</cfloop>
<cfreturn newList>
</cffunction>

<cffunction name="get_sub_areaList" returntype="any">
<cfargument required="no" name="list" default="">
<cfset newList = ''>
<cfloop list="#list#" index="i">
<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from five_star_area_category
where five_star_area_id = #i#
</cfquery>
<cfset newList = newList & search_results.area & ",">
</cfloop>
<cfreturn newList>
<cfreturn search_results>
</cffunction>

<cffunction name="get_sub_area" returntype="any">
<cfargument required="no" name="id" default="">
<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from five_star_area_category
where five_star_area_id = #id#
</cfquery>
<cfreturn search_results>
</cffunction>


<cffunction name="search_poc" returntype="any">
<cfargument required="no" name="paramvar" default="">

<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from poc_detail
#paramvar#
</cfquery>
<cfreturn search_results>
</cffunction>

 
<cffunction returntype="any" name="search_saa">
<cfargument required="no" name="search_term_1" default="not_selected">
<cfargument required="no" name="search_term_2" default="not_selected">
<cfargument required="no" name="search_term_3" default="not_selected">
<cfargument required="no" name="search_term_4" default="">
<cfargument required="no" name="sort" default=""> 
<cfset tmp = "">



<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from poc_detail
where 1=1 and poc_type = 1 and 
<Cfif search_term_1 neq "not_selected">
<cfloop from="#listlen(search_term_1)#" to="1" index="i" step="-1">
	<cfif i neq 1>
		state = '#tostring(ListGetAt(search_term_1,i,","))#' or
	<cfelse>
		state = '#tostring(ListGetAt(search_term_1,i,","))#' and
	</cfif>
</cfloop>

<cfelse>
2=2 AND
</Cfif>
<cfif search_term_2 neq "not_selected">
gov_type = #search_term_2# and
<cfelseif search_term_3 neq "not_selected">
discipline = #search_term_3# and
<cfelseif search_term_4 neq "">
first_name = '#search_term_4#' or last_name like '#search_term_4#' and
</cfif>
3=3
</cfquery>
<cfreturn search_results>
</cffunction>

<cffunction name="search" returntype="any">
<cfargument required="no" name="search_term_1" default="not_selected">
<cfargument required="no" name="search_term_2" default="not_selected">
<cfargument required="no" name="search_term_3" default="not_selected">
<cfargument required="no" name="search_term_4" default="">
<cfargument required="no" name="search_term_5" default="">
<cfargument required="no" name="search_term_6" default="">
<cfargument required="no" name="search_term_7" default="">

<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from poc_detail
where 0=0 and 
<cfif search_term_1 neq "not_selected">
five_star_area like '%#search_term_1#%' and
<cfelseif search_term_2 neq "not_selected">
five_star_category like '%#search_term_2#%' and
<cfelseif search_term_3 neq "not_selected">
overall_level_of_expertise = '#search_term_3#' and
<cfelseif search_term_6 neq "">

<cfloop from="#listlen(search_term_6)#" to="1" index="i" step="-1">
	<cfif i neq 1>
		state = '#tostring(ListGetAt(search_term_6,i,","))#' or
	<cfelse>
		state = '#tostring(ListGetAt(search_term_6,i,","))#' and
	</cfif>
</cfloop>

<cfelseif search_term_4 neq "" >
availability_start >= '#dateformat(search_term_4)#' and 
<cfelseif search_term_5 neq "">
availability_end <= '#dateformat(search_term_5)#' and
<cfelseif search_term_7 neq "">
first_name like '%#search_term_7#%' or last_name like '%#search_term_7#%' and
</cfif>
2=2

</cfquery>
<cfreturn search_results>
</cffunction>

<cffunction name="search_organization" returntype="any">
 <cfargument required="no" name="search_term_1" default="">
<cfargument required="no" name="search_term_2" default="">
<cfargument required="no" name="formVar" default=""> 


<cfquery datasource="#Session.dsn#" name="search_results" >
select * 
from poc_detail
where 0=0 and
<cfif search_term_2 neq "" and search_term_2 neq "not_selected">
company = '#search_term_2#' and
</cfif>
2=2
ORDER BY last_name
</cfquery>
<cfreturn search_results>
</cffunction>

<cffunction returntype="query" name="get_poc_type">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from poc_type 
order by first_choice DESC
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_poc_detail">
<cfargument default="" name="id" required="yes">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from poc_detail where poc_id = #id# 
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_gov_type">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from gov_type 
order by first_choice DESC
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_discipline">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from discipline 
order by first_choice DESC
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_status">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from status order by status desc
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="saa_types">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from saa_type 
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_state">
<cfargument default="" required="yes" name="id">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from states where state_id = '#id#'
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="saa_types_by_id">
<cfargument default="0" required="yes" name="id">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from poc_type where poc_type_id = #id#
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="saa_gov_by_id">
<cfargument default="0" required="yes" name="id">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from gov_type where gov_type_id = #id#
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="saa_discipline_by_id">
<cfargument default="0" required="yes" name="id">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from discipline where discipline_id = #id#
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="saa_status_by_id">
<cfargument default="0" required="yes" name="id">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from status where  id = #id#
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_saa_report">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from poc_detail
where poc_type = 1
</cfquery>
<cfreturn get_roles>
</cffunction>


<cffunction returntype="query" name="get_saa">
<cfargument default="" name="id" required="yes">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from saa_detail where saa_id = #id# 
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_roles">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from prac_roles where username = '#session.username#'
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="query" name="get_selected">
<cfargument default="" name="id" required="yes">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from prac_roles where username = '#id#'
</cfquery>
<cfreturn get_roles>
</cffunction>

<cffunction returntype="any" name="delete_user">
<cfargument default="" name="id" required="yes">
<cfset test = session.queries.get_selected("#session.username#")>
<cfif test.prac_admin eq 1>
<cfquery datasource="#session.dsn#" name="get_roles" >
delete from prac_roles where username = '#id#' and username <> 'ADMIN' and username <> '#session.username#' and username <> 'sys'
</cfquery>
</cfif>
</cffunction>

<cffunction returntype="query" name="get_users">
<cfquery datasource="#session.dsn#" name="get_roles" >
select * from prac_roles
where prac_admin <> 1
order by username
</cfquery>
<cfreturn get_roles>
</cffunction>


<cffunction returntype="query" name="get_reports">
<cfquery datasource="#session.dsn#" name="get_category" >
select * from five_star_reports where user_id = '#session.username#'
</cfquery>
<cfreturn get_category>
</cffunction>

<cffunction returntype="query" name="get_resumes">
<cfargument default="0" name="id" required="no">
<cfquery datasource="#session.dsn#" name="get_category" >
select * from resume_directory where poc_id = #id#
</cfquery>
<cfreturn get_category>
</cffunction>

<cffunction returntype="any" name="get_emails">
<cfargument default="" required="yes" name="id">

<cfset listArray = arraynew(1)>
<cfset email = "">
<cfloop from="1" to="#listlen(id)#" index="i">
<cfquery datasource="#session.dsn#" name="get_category" >
select * from poc_detail where poc_id = #listgetat(id,i)#
</cfquery>
<cfif get_category.email neq "">
<cfset email = email & "#get_category.email#" & ",">
</cfif>

</cfloop>
<cfreturn email>

</cffunction>

<cffunction returntype="any" name="get_attachments">
<cfargument default="" required="yes" name="id">

<cfset listArray = arraynew(1)>
<cfset email = "">
<cfset location = "">
<cfloop from="1" to="#listlen(id)#" index="i">
<cfquery datasource="#session.dsn#" name="get_category" >
select * from resume_directory where poc_id = #listgetat(id,i)#
</cfquery>

<cfif get_category.resume_title neq "">
<cfset email = email & "#get_category.resume_title#" & ",">
</cfif>
<cfif get_category.resume_location neq "">
<cfset location = location & "#get_category.resume_location#" & ",">
</cfif>
</cfloop>
<cfreturn email><cfreturn location>

</cffunction>

<cffunction returntype="any" name="get_report_list">
<cfargument name="id" required="yes" default="">
<cfquery datasource="#session.dsn#" name="get_category" >
select * from five_star_reports where report_id = #id# and user_id = '#session.username#'
</cfquery>
<cfreturn get_category>
</cffunction>

<cffunction returntype="query" name="get_current_report">

<cfquery datasource="#session.dsn#" name="get_category" >
select * from current_selections where user_id = '#session.username#'
</cfquery>
<cfreturn get_category>
</cffunction>

<cffunction returntype="query" name="update_old_report">
<cfargument name="id" required="yes" default="">
<cfargument name="list" required="yes" default="">
<cfquery datasource="#session.dsn#" name="get_category">
update five_star_reports
set 
poc_id = '#list#'
where report_id = #id#
</cfquery>

</cffunction>

<cffunction returntype="query" name="get_report_data">
<cfargument default="" name="id" required="yes">
<cfquery datasource="#session.dsn#" name="get_category" >
select * from five_star_reports where report_id = #id# and user_id = '#session.username#'
</cfquery>
<cfreturn get_category>
</cffunction>

<cffunction returntype="any" name="check_5_star_area">
<cfargument default="" required="yes" name="id">
<cfargument default="" required="yes" name="poc_id">
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from poc_detail where poc_id = #poc_id#
</cfquery>
<cfset temp = ListContains(get_practitioner.five_star_area,"#id#")>
<cfreturn temp>
</cffunction>

<cffunction returntype="any" name="check_5_star_area_sub">
<cfargument default="" required="yes" name="id">
<cfargument default="" required="yes" name="poc_id">
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from poc_detail where poc_id = #poc_id#
</cfquery>
<cfset temp = ListContains(get_practitioner.five_star_category,"#id#")>
<cfreturn temp>
</cffunction>


<cffunction returntype="query" name="five_star_area_category">
<cfquery datasource="#session.dsn#" name="get_5_star_area" >
select * from five_star_areas order by area asc
</cfquery>
<cfreturn get_5_star_area>
</cffunction>

<cffunction returntype="query" name="expertise">
<cfquery datasource="#session.dsn#" name="get_expertise" >
select * from expertise order by expertise asc
</cfquery>
<cfreturn get_expertise>
</cffunction>

<cffunction returntype="query" name="companys">
<cfquery datasource="#session.dsn#" name="get_companys" >
select * from companys order by company asc
</cfquery>
<cfreturn get_companys>
</cffunction>



<cffunction returntype="query" name="get_resume"  >
<cfquery datasource="#session.dsn#" name="get_states" >
select * from states order by state_id asc
</cfquery>
<cfreturn get_states>
</cffunction>

<cffunction returntype="query" name="states"  >
<cfquery datasource="#session.dsn#" name="get_states" >
select * from states order by state_id asc
</cfquery>
<cfreturn get_states>
</cffunction>

<cffunction returntype="query" name="get_5_star_area">

<cfquery name="get_area" datasource="#session.dsn#" >
select * from five_star_areas
</cfquery>
<cfreturn get_area>
</cffunction>	

<cffunction returntype="query" name="get_5_star_area_sub"  >
<cfargument name="id" required="yes" default="">
<cfquery name="get_area_sub" datasource="#session.dsn#" >
select * from five_star_area_category where five_star_area_category_id = #id#
</cfquery>
<cfreturn get_area_sub>
</cffunction>

<cffunction returntype="any" name="fiveStarArea"  >
<cfargument name="id" required="no" default="0">
<cfquery name="get_area_sub" datasource="#session.dsn#" >
select * from five_star_areas where five_star_area_category_id = #id#
</cfquery>

<cfreturn get_area_sub.area>
</cffunction>

<cffunction returntype="any" name="MakListID"  >
<cfargument name="id" required="no" default="0">
<cfargument name="list" required="no" default="">
<cfset new_list = ''>
<cfloop list="#list#" index="i" >

<cfquery name="get_area_sub" datasource="#session.dsn#" >
select * 
from five_star_area_category
where five_star_area_id = #i# and five_star_area_category_id = #id#
</cfquery>
<cfif get_area_sub.recordcount neq 0>
<cfset new_list = new_list  & get_area_sub.area & "<img src='/images/image_delete.gif' class='pointer' id='sub_"  & get_area_sub.five_star_area_id & "' width='10' height='10' >, ">
</cfif>
</cfloop>

<cfreturn new_list>
</cffunction>

<cffunction returntype="any" name="fiveStarSubArea"  >
<cfargument name="id" required="no" default="0">
<cfquery name="get_area_sub" datasource="#session.dsn#" >
select * 
from five_star_area_category
where five_star_area_id = #id#
</cfquery>

<cfreturn get_area_sub>
</cffunction>

<cffunction returntype="query" name="area_sub"  >
<cfargument name="id" required="yes" default="">
<cfquery name="get_area_sub" datasource="#session.dsn#" >
select * from five_star_area_category where five_star_area_id = #id#
</cfquery>
<cfreturn get_area_sub>
</cffunction>

<cffunction returntype="query" name="get_all_category"  >
<cfquery name="get_area_sub" datasource="#session.dsn#" >
select * from five_star_area_category order by five_star_area_category_id asc
</cfquery>
<cfreturn get_area_sub>
</cffunction>



<cffunction returntype="query" name="login_auth" >
<cfargument default="" name="user_id" type="string" required="yes">
<cfargument default="" name="passcode" type="string" required="yes">
<CFQUERY name="auth_login" datasource="#session.dsn#" >
SELECT * FROM prac_roles WHERE username = '#user_id#' AND passcode = '#passcode#'
</CFQUERY>
<cfreturn auth_login>
</cffunction>



<cffunction returntype="query" name="get_practitioner"  >
<cfargument name="id" required="yes" default="">
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from poc_detail where poc_id = #id#
</cfquery>
<cfreturn get_practitioner>
</cffunction>

<cffunction returntype="query" name="get_all" >
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from poc_detail
</cfquery>
<cfreturn get_practitioner>
</cffunction>

<cffunction returntype="query" name="get_all_saa" >
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from poc_detail
</cfquery>
<cfreturn get_practitioner>
</cffunction>

<cffunction returntype="query" name="get_all_poc" >
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from poc_detail
</cfquery>
<cfreturn get_practitioner>
</cffunction>


<cffunction returntype="query" name="current_selections" >
<cfquery name="get_practitioner" datasource="#session.dsn#" >
SELECT current_selections.poc_id, poc_detail.first_name, poc_detail.last_name, poc_detail.state, poc_detail.email, poc_detail.alt_email, poc_detail.poc_type
FROM current_selections INNER JOIN poc_detail ON current_selections.poc_id = poc_detail.poc_id
where current_selections.user_id = '#session.username#'
ORDER BY poc_detail.last_name DESC
</cfquery>
<cfreturn get_practitioner>
</cffunction>

<cffunction returntype="query" name="find_all_current_selections" >
<cfargument default="" required="yes" name="id">
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from current_selections where user_id = '#session.username#'
</cfquery>
<cfreturn get_practitioner>
</cffunction> 

<cffunction returntype="query" name="find_current_selections" >
<cfargument default="" required="yes" name="id">
<cfquery name="get_practitioner" datasource="#session.dsn#" >
select * from current_selections where poc_id = #id#
</cfquery>
<cfreturn get_practitioner>
</cffunction> 

<cffunction name="delete_cart_by_id" >
<cfargument default="" required="yes" name="id">
<cfquery name="delete" datasource="#session.dsn#" >
delete from current_selections where poc_id = #id# AND user_id = '#session.username#'
</cfquery>


</cffunction>

</cfcomponent>